# -*- coding: utf_8 -*-
# @Time : 2021/2/2 10:07
# @Author : l y w
# @Remark : 门禁 execl导出公共方法
from typing import List
from openpyxl import Workbook, styles, utils, load_workbook
from openpyxl.worksheet.datavalidation import DataValidation


class ExportExecl:
    def __init__(self):
        self.wb = Workbook()
        self.ws = self.wb.active
        self.headline = {}
        self.width = 25
        self.height = 20
        self.select = {}

    def __del__(self):
        self.wb.close()

    def merge_cell(self, coord1, coord2):
        """
        合并单元格
        @param coord1:
        @param coord2:
        @return:
        """
        self.ws.merge_cells(":".join([coord1, coord2]))

    def set_attr(self, coord):
        """
        设置属性
        @param coord:
        @return:
        """
        font = styles.Font(name=u'黑体', size=13)
        self.ws[coord].font = font

        # def set_alignment(self, coord):
        alignment = styles.Alignment(horizontal="center", vertical="center")
        self.ws[coord].alignment = alignment

    def set_fill(self, coord, fgColor="DDDDDD"):
        """
        设置颜色
        @param coord:
        @param fgColor:
        @return:
        """
        fill = styles.PatternFill("solid", fgColor=fgColor)
        self.ws[coord].fill = fill

    def insert_data(self, coord, value):
        """
        插入数据
        @param coord:
        @param value:
        @return:
        """
        self.ws[coord].value = value

    def set_column_row(self, coord, width=None, height=None):
        self.ws.column_dimensions[coord[0]].width = width if width else self.width
        self.ws.row_dimensions[int(coord[1])].height = height if height else self.height

    @staticmethod
    def get_letter(header_len):
        """
        获取对应的字母
        @param header_len:
        @return:
        """
        return utils.get_column_letter(header_len)

    def set_header(self, value, header_dict):
        """
        设置表名称
        @param value:
        @param header_dict:
        @return:
        """
        header_coord = "A1"
        self.insert_data(coord=header_coord, value=value)
        self.set_attr(header_coord)
        self.set_fill(header_coord)
        self.merge_cell(header_coord, self.get_letter(len(header_dict)) + "2")
        self.set_column_row(header_coord)

    def set_headline(self, start_row: int, headline_data: dict):
        """
        设置表头
        @param start_row:
        @param headline_data:
        @return:
        """
        start_column = 1
        headline_list = []
        for key, value in headline_data.items():
            coord = self.get_letter(start_column) + str(start_row)
            if self.select:
                select_coord = self.get_letter(start_column) + str(start_row + 1)
                self.set_select(select_coord, key)
            self.insert_data(coord, value)
            self.set_attr(coord)
            self.set_fill(coord, fgColor="000066CC")
            self.set_column_row(coord)

            headline_list.append(key)
            start_column += 1
        return start_row + 1, headline_list

    def set_data(self, start_row, headline_list: list, data_list: List[dict]):
        """
        插入数据
        @param start_row:
        @param headline_list:
        @param data_list:
        @return:
        """
        for set_data_dict in data_list:
            start_column = 1
            for headline in headline_list:
                coord = self.get_letter(start_column) + str(start_row)
                self.insert_data(coord, set_data_dict.get(headline))
                self.set_attr(coord)
                self.set_column_row(coord)
                start_column += 1
            start_row += 1

    def set_select(self, coord, key):
        """
        设置选择框
        @param coord:
        @param key:
        @return:
        """
        if self.select.get(key):
            formula = '"%s"' % (",".join(self.select[key]))

            dv = DataValidation(type="list", formula1=formula, allow_blank=True)
            self.ws.add_data_validation(dv)
            dv.add(self.ws[coord])

    def data_process(self, headline_data_dict):
        """
        增加数据
        @param headline_data_dict:
        @return:
        """
        if not headline_data_dict.get("headline"):
            raise AssertionError("headline数据不存在...")
        if not headline_data_dict.get("data"):
            raise AssertionError("data数据不存在...")
        headline_data = headline_data_dict["headline"]
        start_row = 1
        if headline_data_dict.get("header"):
            self.set_header(headline_data_dict["header"], headline_data)
            start_row = 3
        # self.insert_data("A1","a")
        start_row, headline_list = self.set_headline(start_row, headline_data)
        self.set_data(start_row, headline_list, headline_data_dict["data"])
        return self.wb.save

    def template_download(self, headline_data_dict):
        """
        生成模板
        @param headline_data_dict:
        @return:
        """
        if not headline_data_dict.get("headline"):
            raise AssertionError("headline数据不存在...")
        headline_data = headline_data_dict["headline"]
        start_row = 1
        if headline_data_dict.get("header"):
            self.set_header(headline_data_dict["header"], headline_data)
            start_row = 3
        if headline_data_dict.get("select"):
            self.select = headline_data_dict["select"]

        self.set_headline(start_row, headline_data)
        return self.wb.save

    @classmethod
    def read_execl(cls, file, head_data_dict: dict):
        """
        读取execl
        @param file:
        @param head_data_dict:
        @return:
        """
        if not head_data_dict:
            raise AssertionError("缺失模板")
        self = cls()
        self.wb = load_workbook(file)
        self.ws = self.wb.active
        max_row = self.ws.max_row + 1
        max_column = self.ws.max_column + 1
        start_row = 1
        if head_data_dict.get("header"):
            start_row = 3
        if isinstance(head_data_dict.get("headline"), dict):
            self.headline = {v: k for k, v in head_data_dict["headline"].items()}
        column_list = self.read_headline(start_row, max_column)
        start_row += 1
        return self.read_data(column_list, start_row, max_row, max_column)

    def read_headline(self, start_row, max_column):
        """
        读取表头
        @param start_row:
        @param max_column:
        @return:
        """
        column_list = []

        for column in range(1, max_column):
            coord = self.get_letter(column) + str(start_row)
            execl_headline = self.ws[coord].value
            if self.headline.get(execl_headline):
                column_list.append(self.headline[execl_headline])
        return column_list

    def read_data(self, column_list, start_row, max_row, max_column):
        """
        读取数据
        @param column_list:
        @param start_row:
        @param max_row:
        @param max_column:
        @return:
        """
        result_list = []
        for row in range(start_row, max_row):
            result_dict = {}
            none_num = 1
            for column in range(1, max_column):
                coord = self.get_letter(column) + str(row)
                execl_value = self.ws[coord].value
                if execl_value is None:
                    none_num += 1
                result_dict[column_list[column - 1]] = execl_value if execl_value is not None else ""
            if none_num == max_column:
                continue
            if not result_dict:
                continue
            result_list.append(result_dict)
        return result_list


def get_execl_headline(test_items: dict):
    """
    将test_item转换为转execl所需headline
    """
    headline_dict = dict()
    for v in test_items.values():
        headline_dict[v['name']] = v['name']

    return headline_dict


if __name__ == '__main__':
    data_dict = {
        "header": "批量导入视频 码流(1主、0副) 设备类型(1摄像头、2录像机、3其他) 数据状态(1启用、0禁用)",
        "headline": {
            "group_name": "视频组名称",
            "name": "视频名称",
            "server_ip": "服务器IP",
            "device_ip": "设备IP",
            "url": "URL",
            "port": "端口",
            "username": "用户名",
            "password": "密码",
            "stream_code": "通道",
            "aisle": "码流",
            "device_type_code": "设备类型",
            "vendor": "供应商名称",
            "data_state": "数据状态"
        },
        "select": {"aisle": ["1", "2"], "device_type_code": ["1", "2", "3"], "data_state": ["0", "1"]},
        "data": [
            {
                "badge_number": "12232131211",
                "card_number": "A12345611",
                "door_id": "1",
                "fingerprint": "",
                "name": "刘",
                "serial_num": 9911,
                "state": "停用",
                "update_time": "2021-08-10 15:25:33",
                "id": "7"
            },
            {
                "badge_number": "12232131211",
                "card_number": "A12345611",
                "door_id": "1",
                "fingerprint": "",
                "name": "刘",
                "serial_num": 991,
                "state": "在用",
                "id": "6",
                "update_time": ""
            }
        ]
    }
    # ExportExecl().template_download(data_dict)("./test.xlsx")
